package com.oa.dao.impl;

import java.beans.PropertyDescriptor;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.JDBC4PreparedStatement;
import com.oa.bean.BranchInfo;
import com.oa.bean.DepartInfo;
import com.oa.bean.PreContract;
import com.oa.bean.Schedules;
import com.oa.bean.UserInfo;
import com.oa.dao.SuperDao;
import com.oa.util.DBUtil;
import com.oa.util.MyDateUtil;
import com.oa.util.PageUtil;

public class CommonDao<T> extends DBUtil implements SuperDao<T> {

	@Override
	public List<T> find(T t,PageUtil<T> pu) {
		List<T> ts=new ArrayList<T>();		
		Class<?> clazz=t.getClass();
		String sql="select * from "+t.getClass().getSimpleName();
		System.out.println("全查sql==>"+sql);
		return getDataList(ts, clazz, getPaging(sql,pu));
	}

	@Override
	public List<T> query(Map<String, Object> params,T t) {
		List<T> ts=new ArrayList<T>();		
		Class<?> clazz=t.getClass();
		String sql="select * from "+t.getClass().getSimpleName()+" where 1=1 ";
		List<String> keys=new ArrayList<String>();
		List<String> values=new ArrayList<String>();
		
		for (String s : params.keySet()) {
			keys.add(s);
			values.add(params.get(s).toString());
		}
		StringBuffer sb=new StringBuffer();
		if (keys.size()>1) {
			//条件查询
			for (int i = 0; i < keys.size(); i++) {
				sb.append(" and"+keys.get(i)+"="+values.get(i));
			}
		}else {
			sb.append(" and"+keys.get(0)+" like '%"+values.get(0)+"%'");
		}
		sql+=sb.toString();
		return getDataList(ts, clazz, sql);
	}

	@Override
	public int update(T t) {
				String tableName=t.getClass().getSimpleName();
				String sql="update "+tableName+" set ";				
				String idName="";
				String idValue="";
				Field[] fields=t.getClass().getDeclaredFields();
				for (int i = 0; i < fields.length; i++) {
					String fieldName=fields[i].getName();
					PropertyDescriptor pd;
					try {
						pd = new PropertyDescriptor(fieldName, t.getClass());
						Method method=pd.getReadMethod();
						String fieldValue= method.invoke(t, null).toString();
						System.out.println("对象的值==>"+fieldValue);
						if (i==0) {
							idName=fieldName;
							idValue=fieldValue;
							continue;
						}						
						String typeName=fields[i].getGenericType().getTypeName();
						if (typeName.equals("java.lang.String")||typeName.equals("java.sql.Date")) {
							sql+=fieldName+"='"+fieldValue+"',";
						}else {
							sql+=fieldName+"="+fieldValue+",";
						}			
					}
					catch (Exception e) {
						e.printStackTrace();
					} 		
				}		 
				sql=sql.substring(0, sql.length()-1);
				sql+=" where "+idName+"="+idValue;
				System.out.println("修改方法sql==>"+sql);
				return getUpdateResult(sql);
	}

	@Override
	public int add(T t) {
				String tableName=t.getClass().getSimpleName();				
				String sql="insert "+tableName+" values(";				
				Field[] fields=t.getClass().getDeclaredFields();
				for (int i = 0; i < fields.length; i++) {
					String propertyName=fields[i].getName();
					try {
						PropertyDescriptor pd=new PropertyDescriptor(propertyName, t.getClass());
//						System.out.println(pd.getDisplayName());
						Method method=pd.getReadMethod();
//						System.out.println(method);
						if (method.invoke(t)==null) {
							sql+="null,";
						}else {
							String propertyValue=method.invoke(t, null).toString();		
//							System.out.println("属性值:==>"+propertyValue);
							String typeName=fields[i].getGenericType().getTypeName();
//							System.out.println("当前属性的数据类型是：==>"+typeName);
							if (typeName.equals("java.lang.String")) {
								sql+="'"+propertyValue+"',";
							}else {
								sql+=propertyValue+",";
							}								
							int num=0;		//num是属性/变量名，0是属性值/变量值							
						}
					} catch (Exception e) {
						e.printStackTrace();
					}
				}				
				sql=sql.substring(0, sql.length()-1);
				sql+=")";
//				System.out.println("添加==>"+sql);
				return getUpdateResult(sql);
	}

	@Override
	public int delete(T t) {
				String tableName=t.getClass().getSimpleName();				
				String idName="";
				String idValue="";
				try {
					Field f=t.getClass().getDeclaredFields()[0];
					idName=f.getName();
					PropertyDescriptor pd=new PropertyDescriptor(idName, t.getClass());
					Method method=pd.getReadMethod();
					idValue=method.invoke(t, null).toString();
				} catch (Exception e) {
					e.printStackTrace();
				} 
				String sql="delete from "+tableName+" where "+idName+"="+idValue;
				System.out.println(sql);
				return getUpdateResult(sql);
	}
		
	@Override
	public T findById(T t,String... id) {
		Field idField=t.getClass().getDeclaredFields()[0];
		String idName=idField.getName();
		String sql="select * from "+t.getClass().getSimpleName()+" where "+idName+"=";
		if (id==null||id.length<1) {
			try {
				idField.setAccessible(true);
				String idValue=idField.get(t).toString();
				System.out.println("从对象中获取到的id值为==>"+idValue);
				sql+=idValue;
			} catch (Exception e) {
				e.printStackTrace();
			}
		}else {
			sql+=id;
		}
		List<T> ts=new ArrayList<T>();
		System.out.println("单查sql==>"+sql);
		ts=getDataList(ts, t.getClass(), sql);
		if (ts.size()<1) {
			return null;
		}
		return ts.get(0);
	}
	
	//getDataList
	protected List<T> getDataList(List<T> ts, Class<?> clazz, String sql) {
		try {
			super.open_db();
			super.pst=super.conn.prepareStatement(sql);
			super.rs=super.pst.executeQuery();
			while (super.rs.next()) {
				T t2=(T)clazz.newInstance();
				Field[] fields=clazz.getDeclaredFields();
				for (int i = 0; i < fields.length; i++) {
					String fieldName=fields[i].getName();
					PropertyDescriptor pd=new PropertyDescriptor(fieldName, clazz);
					Method method=pd.getWriteMethod();
					String typeName=fields[i].getGenericType().getTypeName();
					switch (typeName) {
					case "int":
						method.invoke(t2, rs.getInt(fieldName));	
						break;
					case "java.lang.String":
						method.invoke(t2, rs.getString(fieldName));	
						break;
					case "double":
						method.invoke(t2, rs.getDouble(fieldName));	
						break;
					case "java.lang.Integer":
						method.invoke(t2, rs.getInt(fieldName));	
						break;	
					case "java.sql.Date":
						method.invoke(t2, rs.getDate(fieldName));
						break;
					case "java.lang.Long":
						method.invoke(t2, rs.getLong(fieldName));
						break;
					case "java.sql.Timestamp":
						method.invoke(t2, rs.getTimestamp(fieldName));
					default:						
						break;
					}
				}
				ts.add(t2);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			super.close_db();
		}
		return ts;
	}
	
	//getUpdateResult
	private int getUpdateResult(String sql) {
		int result=0;
		try {
			open_db();
			pst=conn.prepareStatement(sql);
//			System.out.println("修改代码==>"+((JDBC4PreparedStatement)pst).asSql());
			result=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return result;
	}

	//分页方法
	protected String getPaging(String sql,PageUtil<T> pu) {
		if (pu!=null) {
			int begin=pu.getBegin();
			int pageSize=pu.getPageSize();
			return sql+" limit "+begin+","+pageSize;
		}else {
			return sql;
		}
	}
	
	//获取总数据条数
	@Override
	public int getTotalCount(T t) {
		String tableName=t.getClass().getSimpleName();
		String sql="select count(*) from "+tableName;
		int totalCount=0;
		try {
			open_db();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while (rs.next()) {
				rs.getInt(0);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return totalCount;
	}
	
	//登录查询用户
	@Override
	public UserInfo findOne(UserInfo user) {	
		String sql="select * from UserInfo where userName='"+
					user.getUserName()+
					"' and pwd='"+
					user.getPwd()+
					"'";
//		System.out.println(sql);
		UserInfo u=null;
		try {
			open_db();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while (rs.next()) {	
				u=new UserInfo();
				u.setUserId(rs.getInt("userId"));
				u.setUserName(rs.getString("userName"));
				u.setDepartId(rs.getInt("departId"));
				u.setGender(rs.getInt("gender"));
				u.setRoleId(rs.getInt("roleId"));
				u.setUserState(rs.getInt("userState"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return u;
	}

	//条件单查
	@Override
	public T findOne(Map<String, Object> param, T t) {
		String tableName=t.getClass().getSimpleName();
		String sql="select * from "+tableName+" where ";
		for (String str : param.keySet()) {
			sql+=str+"="+param.get(str);
		}
		List<T> ts=new ArrayList<T>();
		System.out.println(sql);
		return getDataList(ts, t.getClass(), sql).get(0);
	}

	@Override
	public int getCountCondition(Map<String, Object> param, T t) {
		String tableName=t.getClass().getSimpleName();
		String sql="select count(*) msgCount from "+tableName+" where ";
		for (String string : param.keySet()) {
			sql+=string+"="+param.get(string);
		}
		System.out.println(sql);
		List<T> ts=new ArrayList<T>();
		int totalCount=0;
		try {
			open_db();
			pst=conn.prepareStatement(sql);
			rs=pst.executeQuery();
			while (rs.next()) {
				totalCount=rs.getInt("msgCount");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return totalCount;
	}

	public List<BranchInfo> getBranchTree(){
		List<BranchInfo> bs=getBranchInfos();
		for (BranchInfo b : bs) {
			List<DepartInfo> ds=getDepts(b.getBranchId());
			for (DepartInfo d : ds) {
				List<UserInfo> us=getUsers(d.getDepartId());
				d.setUsers(us);
			}
			b.setDepts(ds);
		}
		return bs;
	}
	
	private List<BranchInfo> getBranchInfos(){
		String sqlBranch="select * from BranchInfo";
		List<BranchInfo> branchInfos=new ArrayList<BranchInfo>();

		try {
			open_db();
			pst=conn.prepareStatement(sqlBranch);
			rs=pst.executeQuery();
			while (rs.next()) {
				BranchInfo branch=new BranchInfo();
				branch.setBranchId(rs.getInt("branchId"));
				branch.setBranchName(rs.getString("branchName"));
				branchInfos.add(branch);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return branchInfos;
	}

	private List<DepartInfo> getDepts(Integer branchId) {
		String sql="select * from departInfo where branchId=?";
		List<DepartInfo> ds=new ArrayList<DepartInfo>();
		try {
			open_db();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, branchId);
			System.out.println(((JDBC4PreparedStatement)pst).asSql());
			rs=pst.executeQuery();
			while (rs.next()) {
				DepartInfo d=new DepartInfo();
				d.setDepartId(rs.getInt("departId"));
				d.setDepartName(rs.getString("departName"));
				ds.add(d);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return ds;
	}

	private List<UserInfo> getUsers(Integer departId) {
		String sql="select * from userInfo where departId=?";
		List<UserInfo> us=new ArrayList<UserInfo>();
		try {
			open_db();
			pst=conn.prepareStatement(sql);
			pst.setInt(1, departId);
			rs=pst.executeQuery();
			while (rs.next()) {
				UserInfo u=new UserInfo();
				u.setUserId(rs.getInt("userId"));
				u.setUserName(rs.getString("userName"));
				us.add(u);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return us;
	}
	
	@Override
	public boolean addSchedule(Schedules sInfo, PreContract pInfo,String beginTime,String endTime, String createTime) {
		String sql="{call addSchedule(?,?,?,?,'"+beginTime+"','"+endTime+"',?,?,'"+createTime+"',?,?)}";
		int result=0;
//		boolean rs=false;
//		'主题',
//		'地点',
//		'1',
//		'2020/8/16 16:29:00',
//		'2020/8/16 16:32:00',
//		'进行初次谈判',
//		'黄永',
//		'2020/8/16 4:28:09',
//		0,
//		2
		try {
			open_db();
			CallableStatement cs=conn.prepareCall(sql);
			cs.setString(2, sInfo.getTitle());
			cs.setString(3, sInfo.getAddress());
			cs.setInt(4, sInfo.getMeetingId());
			cs.setString(5, sInfo.getSchContent());
			cs.setString(6, sInfo.getCreateUser());
			cs.setInt(7, sInfo.getIfPrivate());
			cs.setString(8, pInfo.getUserId());
//			System.out.println(((JDBC4PreparedStatement)cs).asSql());
			cs.registerOutParameter(1, Types.SMALLINT);
			cs.execute();
			result=cs.getInt(1);
//            //如果第一个结果是ResultSet对象，则返回true；如果第一个结果是更新、添加、修改或者没有结果，则返回 false 
//            boolean issuccess=cs.execute();
//            //成功返回true，失败返回false
//            System.out.println(issuccess);
			System.out.println(result);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return result>0;
	}

	@Override
	public List<DepartInfo> findGivenDeparts(String branchId) {
		String sql="select * from departInfo where branchId=?";
		List<DepartInfo> ds=new ArrayList<DepartInfo>();
		try {
			open_db();
			pst=conn.prepareStatement(sql);
			pst.setString(1, branchId);
			rs=pst.executeQuery();
			while (rs.next()) {
				DepartInfo d=new DepartInfo();
				d.setDepartId(rs.getInt("departId"));
				d.setDepartName(rs.getString("departName"));
				ds.add(d);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			close_db();
		}
		return ds;
	}
	
	/***
	 * 按条件查询日程
	 */
	@Override
	public List<Schedules> querySchedules(String deptId, String userName, String sDate) {
//		s.setScheduleId(rs.getInt("schedueId"));
//		s.setTitle(rs.getString("title"));
//		s.setBeginTime(rs.getTimestamp("beginTime"));
		String sql="select * from schedules s,precontract p,userinfo u " + 
				"where u.userId=p.userId and p.scheduleId=s.scheduleId "+
				"and beginTime between ? and ? ";
		List<Schedules> schs=new ArrayList<Schedules>();
		
		if (!userName.isEmpty()&&!userName.equals(null)&&userName!="") {
			sql+="and userName like ?";
		}else {
			sql+="and departId = ?";
		}
		try {
			open_db();
			pst=conn.prepareStatement(sql);

			pst.setTimestamp(1, MyDateUtil.strToTimeStamp((sDate+" 00:00:00"),"yyyy-MM-dd HH:mm:ss")); 
			pst.setTimestamp(2, MyDateUtil.strToTimeStamp((sDate+" 23:59:59"),"yyyy-MM-dd HH:mm:ss")); 					
			if (!userName.isEmpty()&&!userName.equals(null)&&userName!="") {
				pst.setString(3, userName);
			}else {
				pst.setString(3, deptId);
			}			
			rs=pst.executeQuery();
			while (rs.next()) {
				Schedules s=new Schedules();
				s.setScheduleId(rs.getInt("scheduleId"));
				s.setTitle(rs.getString("title"));
				s.setBeginTime(rs.getTimestamp("beginTime"));			
				schs.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close_db();
		}
		return schs;
	}
	
	

}






